﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MaxLeaf.Platform.Contract;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;
using System.Transactions;
using System.Data.SqlClient;
using System.Data.Common;
using System.Diagnostics;
using CommonsLib.Settings;
using CommonsLib.Utility;
using System.Data;

using System.Security.Cryptography;
using MaxLeaf.Platform.Functions;
using MaxLeaf.Platform.Commons;

namespace MaxLeaf.Platform.DALImplementation
{
    public class imStoresReqHdrDAL : BaseDAL
    {

        protected override string  GetCondForPage(DBAccess dbcon, object model, List<Cond> lstCond)
        {
            string strCond = base.GetCondForPage(dbcon, model, lstCond);
            imStoresReqHdrDataModel tModel = (imStoresReqHdrDataModel)model;
            foreach (Cond cond in lstCond)
            {
                if (cond.PropName.Equals("Func_GetApproBill"))
                {
                    if (!string.IsNullOrEmpty(strCond))
                    {
                        strCond += " and ";
                    }
                    strCond += "  dbo.CanApproBill(finterid," + tModel.CompanyID + ","+ tModel.UI_UserID +" )=1 ";
                    continue;
                }
                if (cond.PropName.Equals("Func_GetDepartAppro"))
                {
                    using (DBAccess dbBaseCon = new DBAccess(DataFunc.GetConnectionInfo(Const.DB_SCPBase)))
                    {
                        bool blnDepartAppro = CvtFunc.ToBoolean(dbBaseCon.GetValue("select DepAppro from muser_info where depid=@DepId and UserID=@UserID"
                                                                  , new SqlParameter[] { new SqlParameter("@DepId", tModel.DepartID), new SqlParameter("@UserID", tModel.UI_UserID) }));

                        if (blnDepartAppro)
                        {
                            if (!string.IsNullOrEmpty(strCond))
                            {
                                strCond += " and ";
                            }
                            strCond += "  DepartConfirmed =0 and DocStatus=1 and DepartID='" + tModel.DepartID + "' and CompanyID='" + tModel.CompanyID + "' ";
                        }
                        else
                        {
                            strCond = " 1=0 ";
                        }
                    }
                    continue;
                }
                if (cond.PropName.Equals("Func_GetItem"))
                {
                    if (!string.IsNullOrEmpty(strCond))
                    {
                        strCond += " and ";
                    }
                    string[] arr = cond.Value.ToString().Split('|');
                    strCond += string.Format(@" exists(select d.* from  imStoresReqDtl d  where d.hdrid=imStoresReqHdr.finterid 
                                    and d.ItemID='{0}' and d.Price='{2}' and d.UnitID='{1}'
                                )"
                        , arr[0]
                        , arr[1]
                        , arr[2]);
                    continue;
                }
            }
            return strCond;
        }

        /// <summary>
        ///    （如果有总公司）这个采购申请单要提交到总公司的领用申请单来进行级别审批（审核）
        ///     重新生成（Insert）领用申请单表头（更换companyID 为总公司ID号），OrigCompanyID为子公司ID，OrigFInterID为原领用申请单表头FInterID
        ///     重新生成（Insert）领用申请单表体（更换companyID 为总公司ID号），OrigDtlID为子公司原领用申请单表体DtlID
        /// </summary>
        public bool GeneratNewStoresReq(DBAccess dbcon, imStoresReqHdrDataModel imOrigHdrModel, imStoresReqHdrDataModel imNewHdrModel,CategroyApproDataModel model)
        {
            imNewHdrModel.CheckType = imOrigHdrModel.CheckType;
            imNewHdrModel.DepartID = imOrigHdrModel.DepartID;
            imNewHdrModel.CurApproLevel = 1;
            imNewHdrModel.DocDate = imOrigHdrModel.DocDate;
            imNewHdrModel.DocStatus = imOrigHdrModel.DocStatus;
            imNewHdrModel.DocType = imOrigHdrModel.DocType;
            imNewHdrModel.ApprovalCategorySeqno = imOrigHdrModel.ApprovalCategorySeqno;
            imNewHdrModel.EnterUserID = imOrigHdrModel.EnterUserID;
            imNewHdrModel.Posted = false  ;
            imNewHdrModel.Remark = imOrigHdrModel.Remark;
            imNewHdrModel.ReqUserID = imOrigHdrModel.ReqUserID;
            imNewHdrModel.OrigFInterID = imOrigHdrModel.Key;
            imNewHdrModel.OrigOrdNo = imOrigHdrModel.OrdNo;
            imNewHdrModel.ForeignKey = model.Group_FInterID;
            imNewHdrModel.DepartConfirmed = imOrigHdrModel.DepartConfirmed;            
            new imStoresReqHdrDAL().Update(dbcon, imNewHdrModel);
            return true;
        }

        public bool GenetateNewStoresReqDtl(DBAccess dbcon, imStoresReqDtlDataModel imOrigDtlModel , imStoresReqDtlDataModel imNewDtlModel)
        {
            imNewDtlModel.ApprovalQty = imOrigDtlModel.ApprovalQty;
            imNewDtlModel.ItemID = imOrigDtlModel.ItemID;
            imNewDtlModel.Price = imOrigDtlModel.Price;
            imNewDtlModel.ReqQty = imOrigDtlModel.ReqQty;
            imNewDtlModel.UnitID = imOrigDtlModel.UnitID;
            imNewDtlModel.WebPrice = imOrigDtlModel.WebPrice;
            imNewDtlModel.Subtotal = imOrigDtlModel.Subtotal;
 
            return true;
        }

        public double GetMonthDepAmt(DBAccess dbcon, int intDepID)
        {
            string strSql = string.Format(@"select  sum(d.Subtotal) 
                from  imStoresReqDtl d inner join imStoresReqHdr h on d.hdrid=h.finterid
                inner join imStoresReqHdr origH on h.OrigFInterID=origH.finterid
                where origH.DepartID=@DepartID and origH.DocDate>=@st and origH.DocDate<@ed
                and  h.OrigFInterID<>0 
                 and h.Is_Delete=0 and h.posted=1 and d.Is_Delete=0 
            ");

            string strStDate, strEdDate;
            GetMonthDateRange(out strStDate, out strEdDate);

            return CvtFunc.ToDouble(dbcon.GetValue(strSql, new SqlParameter[] { 
                new SqlParameter("@DepartID", intDepID.ToString()) 
                ,new SqlParameter("@st", strStDate) 
                ,new SqlParameter("@ed",strEdDate) 
            }));

            
        }

        private void GetMonthDateRange(out string strStDate, out string strEdDate)
        {
            DateTime dtSt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
            DateTime dtEd = dtSt.AddMonths(1);

            strStDate = CvtFunc.ToDateString(dtSt);
            strEdDate = CvtFunc.ToDateString(dtEd);
        }

        public double GetMonthCompanyAmt(DBAccess dbcon, int intCompanyID)
        {
            string strSql = string.Format(@"select  sum(d.Subtotal) 
                from  imStoresReqDtl d inner join imStoresReqHdr h on d.hdrid=h.finterid
                inner join imStoresReqHdr origH on h.OrigFInterID=origH.finterid
                where origH.CompanyID=@CompanyID and origH.DocDate>=@st and origH.DocDate<@ed
                and  h.OrigFInterID<>0 
                 and h.Is_Delete=0 and h.posted=1 and d.Is_Delete=0 
            ");

            string strStDate, strEdDate;
            GetMonthDateRange(out strStDate, out strEdDate);
            
            return CvtFunc.ToDouble(dbcon.GetValue(strSql, new SqlParameter[] { 
                new SqlParameter("@CompanyID", intCompanyID.ToString()) 
                ,new SqlParameter("@st", strStDate) 
                ,new SqlParameter("@ed",strEdDate) 
            }));
        }

        internal static string OverviewTableName(object model)
        {
            
            const string Fields_Sql = @"select h.* from imStoresReqHdr h";

            string strSql1 = string.Format(@"
                {0}  where h.ReqUserID={1} and h.is_delete=0               
                union
                {0} where exists(select u.DepAppro from {2}muser_info u where u.UserID={1} and u.depid=h.DepartID and u.DepAppro=1) and h.is_delete=0
                union
                {0} where dbo.CanApproBill(h.finterid,h.CompanyID ,{1} )=1 and h.is_delete=0
                union
                {0} inner join SCPApproHistory B on h.FInterID=b.FInterID and b.AppUserID={1} and h.is_delete=0
                "
                , Fields_Sql
                ,DataFunc.GetPropValue(model, "UI_UserID")
                ,GetBaseDatabase()
                );

            string strSql2 = string.Format("select h.* from imStoresReqHdr h inner join ({0}) a  on h.finterid=a.origfinterid and h.is_delete=0", strSql1);
            string strSql = string.Format(@"(
select h.*,isnull(d.TotAmt,0) as TotAmt from (
                {0}
                union
                {1}
            ) h left join (select HdrId,sum(Subtotal) as TotAmt  from imStoresReqDtl where is_delete=0 group by HdrId) d on h.finterid=d.HdrId
) h
            "
                , strSql1
                , strSql2
                );

            return strSql;
        }
    }
}
